package com.bootcamp.s3_1013.database._1018_transaction;

import com.bootcamp.s3_1013.database.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * @program: myClassCode
 * @description: two differnt way to insert 1000 data and compare its speed;
 * prepare statement to query a piece of data using primary key and normal value, compare its speed.
 * @author: Keyu Li
 * @create: 2021-10-18 19:46
 **/


public class Ex {
    static final int NUM = 10000*1000;
    public static void main(String[] args) {
        // connection
        Connection connection = JDBCUtils.getConnection();
        // create table
//        createTable(connection);
        // calculate time
        long[] stop = new long[5];
        stop[0] = System.currentTimeMillis();
        // truncate table and insert 1000 data using statement
//        insertByStateBatch(connection, NUM);
        stop[1] = System.currentTimeMillis();
        // truncate table and insert 1000 data using prepare statement
        insertByPreStateBatch(connection, NUM);
        stop[2] = System.currentTimeMillis();
        // query a primary id and its vale
        int key = 234;
        int value = 234;
        queryKey(connection, key);
        stop[3] = System.currentTimeMillis();
        queryValue(connection, value);
        stop[4] = System.currentTimeMillis();
        // print data
        System.out.println("total num: "+NUM);
        System.out.print("statement\t");
        System.out.print("prepare state\t");
        System.out.print("query key\t");
        System.out.println("query value\t");
        for (int i = 0; i < stop.length - 1; i++) {
            System.out.print((int) (stop[i + 1] - stop[i]) + "\t\t\t");
        }
        // close resources
        JDBCUtils.closeSources(connection, null, null);
    }


    private static void queryValue(Connection connection, int num) {

        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.executeQuery("select * from thousand where num=" + num);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeSources(null, statement, null);
        }
    }

    private static void queryKey(Connection connection, int id) {
        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.executeQuery("select * from thousand where id=" + id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeSources(null, statement, null);
        }
    }

    private static void insertByPreStateBatch(Connection connection, int num) {
        PreparedStatement preparedStatement = null;

        try {
            String sql = "insert into thousand values(null,?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= num; i++) {
                preparedStatement.setInt(1, num-i);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeSources(null, preparedStatement, null);
        }
    }

    private static void insertByStateBatch(Connection connection, int num) {

        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.addBatch("truncate thousand");
            for (int i = num; i > 0; i--) {
                String sql = "insert into thousand values(null,+" + num + ")";
                statement.addBatch(sql);
            }
            statement.addBatch("truncate thousand");
            statement.executeBatch();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeSources(null, statement, null);
        }
    }

    private static void createTable(Connection connection) {
        try {
            Statement statement = connection.createStatement();
            statement.executeUpdate("create table thousand(id int primary key auto_increment,num int)");
            JDBCUtils.closeSources(null, statement, null);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
